package zy.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import zy.entity.base.size.T_Base_Size;
import zy.entity.base.type.T_Base_Type;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class BaseDaoImpl implements BaseDao {

	@Resource
	protected NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	@Resource
	protected NamedParameterJdbcTemplate train_namedParameterJdbcTemplate;

	public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
		return namedParameterJdbcTemplate;
	}

	public NamedParameterJdbcTemplate getTrain_namedParameterJdbcTemplate() {
		return train_namedParameterJdbcTemplate;
	}

	@Override
	public Map<String, Object> map(String sql) {
		return namedParameterJdbcTemplate.getJdbcOperations().queryForMap(sql);
	}
	
	/**
	 * 针对【后台】的方法
	 * 用到此方法的只有总公司和分公司的帐号类型，其他地方调用可以会错，请斟酌
	 * @param type 店铺的类型，only 是否只查自己下的店铺0否1是
	 * */
	public String getShopSQL(Object type,Object only){
		StringBuffer sql = new StringBuffer("");
		if(null != type){
			sql.append(" join common_type");
			sql.append(" ON ty_id=sp_shop_type");
			if("1".equals(StringUtil.trimString(type))){//总公司
				if(null != only && "1".equals(StringUtil.trimString(only))){
					sql.append(" AND ty_three=:shop_type");
				}else{
					sql.append(" AND ty_one=:shop_type");
				}
			}
			if("2".equals(StringUtil.trimString(type))){//分公司
				if(null != only && "1".equals(StringUtil.trimString(only))){//只查自己下面的店铺
					sql.append(" AND ty_five=:shop_type");
				}else{
					sql.append(" AND ty_two=:shop_type");
				}
			}
			sql.append(" AND sp_upcode=:shop_code");
		}
		return sql.toString();
	}
	
	/**
	 * 针对【前台】的方法
	 * 用到此方法的只有总公司和分公司的帐号类型，其他地方调用可以会错，请斟酌
	 * @param type 店铺的类型，only 是否只查自己下的店铺0否1是
	 * */
	public String getSellShopSQL(Object type,Object only){
		StringBuffer sql = new StringBuffer("");
		if(null != type){
			sql.append(" join common_type");
			sql.append(" ON ty_id=sp_shop_type");
			if("1".equals(StringUtil.trimString(type))){//总公司
				if(null != only && "1".equals(StringUtil.trimString(only))){
					sql.append(" AND ty_three=:shop_uptype");
				}else{
					sql.append(" AND ty_one=:shop_uptype");
				}
			}
			if("2".equals(StringUtil.trimString(type))){//分公司
				if(null != only && "1".equals(StringUtil.trimString(only))){
					sql.append(" AND ty_five=:shop_uptype");
					
				}else{
					sql.append(" AND ty_two=:shop_uptype");
				}
			}
			sql.append(" AND sp_upcode=:shop_upcode");
		}
		return sql.toString();
	}
	/**
	 * 针对【前台】的方法
	 * 用到此方法的只有总公司和分公司的帐号类型，其他地方调用可以会错，请斟酌
	 * @param type 店铺的类型，only 是否只查自己下的店铺0否1是,code上级店铺的编号不好预处理的方法
	 * */
	public String getSellShopSQL(Object type,Object only,Object code){
		StringBuffer sql = new StringBuffer("");
		if(null != type){
			sql.append(" join common_type");
			sql.append(" ON ty_id=sp_shop_type");
			if("1".equals(StringUtil.trimString(type))){//总公司
				if(null != only && "1".equals(StringUtil.trimString(only))){
					sql.append(" AND ty_three="+type);
				}else{
					sql.append(" AND ty_one="+type);
				}
			}
			if("2".equals(StringUtil.trimString(type))){//分公司
				if(null != only && "1".equals(StringUtil.trimString(only))){
					sql.append(" AND ty_five="+type);
					
				}else{
					sql.append(" AND ty_two="+type);
				}
			}
			sql.append(" AND sp_upcode="+code);
		}
		return sql.toString();
	}
	
	public String cashNumber(String sell_type,final String em_code,final String shop_code,final Integer companyid){
		String new_number = (String)namedParameterJdbcTemplate.getJdbcOperations().execute(new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				StringBuffer _sql = new StringBuffer("{call p_sell_number(?,?,?,?,?)}");// 调用的sql   
                CallableStatement cs = conn.prepareCall(_sql.toString());   
                cs.setInt(1, companyid);// 设置输入参数的值   
                cs.setString(2, shop_code);// 设置输入参数的值   
                cs.setString(3, em_code);// 设置输入参数的值   
                cs.setString(4, DateUtil.getYearMonthDate());// 设置输入参数的值   
                cs.registerOutParameter(5, java.sql.Types.CHAR);// 注册输出参数的类型   
                return cs;   
			}
		},new CallableStatementCallback<Object>() {
			@Override
			public Object doInCallableStatement(CallableStatement cs)
					throws SQLException, DataAccessException {
				cs.executeUpdate(); 
				String number = cs.getString(5);
				return number;
			}
		});
		//零售收银编号格式：状态+日期+银员编号+流水号 如01612120120005 去掉店铺编号，因为一个收银员编号只能存在一条数据
		return sell_type+DateUtil.getYearMonthDateYYMMDD()+em_code+new_number;
	}

	public String getSizeSQL(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT sz_id,sz_code,sz_name");
		sql.append(" FROM t_base_sizelist szl");
		sql.append(" JOIN t_base_size sz ON szl_sz_code = sz_code AND szl.companyid = sz.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND szl_szg_code = :szg_code");
		sql.append(" AND szl.companyid = :companyid");
		sql.append(" ORDER BY szl_order");
		return sql.toString();
	}
	
	public String getColorBraSQL(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT DISTINCT cr_name,cr_code,IFNULL(br_code,'') AS br_code,IFNULL(br_name,'') AS br_name");
		sql.append(" FROM t_base_product_color pdc");
		sql.append(" JOIN t_base_color cr ON cr_code = pdc_cr_code AND cr.companyid = pdc.companyid");
		sql.append(" LEFT JOIN t_base_product_br pdb ON pdb_pd_code = :pd_code AND pdb.companyid = pdc.companyid");
		sql.append(" LEFT JOIN t_base_bra br ON br_code = pdb_br_code AND br.companyid = pdc.companyid");
		sql.append(" WHERE pdc_pd_code = :pd_code AND pdc.companyid = :companyid");
		sql.append(" ORDER BY cr_name,br_name");
		return sql.toString();
	}
	
	public String getStockSQL(){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT sd_sz_code AS sz_code,sd_cr_code AS cr_code,IFNULL(sd_br_code,'') AS br_code,sd_amount AS amount");
		sql.append(" FROM t_stock_data sd");
		sql.append(" WHERE 1=1");
		sql.append(" AND sd_pd_code = :pd_code");
		sql.append(" AND sd_dp_code = :dp_code");
		sql.append(" AND sd.companyid = :companyid");
		return sql.toString();
	}

	public List<T_Base_Size> loadProductSize(String pd_code,Integer companyid){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT pd_szg_code ");
		sql.append(" FROM t_base_product");
		sql.append(" WHERE pd_code = :pd_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		String pd_szg_code = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("pd_code", pd_code).addValue("companyid", companyid), String.class);
		return namedParameterJdbcTemplate.query(getSizeSQL(),
				new MapSqlParameterSource().addValue("szg_code", pd_szg_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Base_Size.class));
	}
	
	public List<T_Base_Type> listTypeByUpCode(String tp_upcode,Integer companyid){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT tp_code,tp_name,tp_upcode ");
		sql.append(" FROM t_base_type t");
		sql.append(" WHERE 1=1");
		sql.append(" AND (tp_upcode = :tp_upcode OR tp_code = :tp_upcode)");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("tp_upcode", tp_upcode).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Base_Type.class));
	}
	
	public List<T_Base_Type> listAllType(Integer companyid){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT tp_code,tp_name,tp_upcode ");
		sql.append(" FROM t_base_type");
		sql.append(" WHERE companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(),new MapSqlParameterSource().addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Base_Type.class));
	}
	
}